<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="java.sql.*" import="java.io.*"%>
    
<!--main content example start-->
<div id="main">

<%!
	//declaring public variables
	private String driver;
	private String connection;
	private String user;
	private String password;
%>		
						
<% 
	//to link back to the login.jsp page, uncomment the following:
	//String sessionFlag = ((String) session.getAttribute("login"));
	driver = application.getInitParameter("oracle_driver");
	connection = application.getInitParameter("oracle_db");
	user = application.getInitParameter("oracledb_user");
	password = application.getInitParameter("oracledb_password");
%>

			<h2>Agencies</h2>
			<%
					//connect to database using web.xml stored parameters
					Class.forName(driver);
					Connection connectAgencies = DriverManager.getConnection(connection, user, password);
					java.sql.Statement stmntAgencies = connectAgencies.createStatement();
					
					ResultSet resSetAgencies = stmntAgencies.executeQuery("SELECT agncyaddress || ' , ' || agncycity || ', ' || " + 
							"agncyprov || '   ' || agncypostal || ' ' || agncycountry as \"Address\"," +
							"agncyphone as \"Phone\", agncyfax as \"Fax\" from ictoosd.agencies order by agncycity");
					ResultSetMetaData resSetMetaDataAgencies = resSetAgencies.getMetaData();
					
					if (resSetMetaDataAgencies != null)//(resSetAgencies.next())
					{
						//print the beginning of the table and row
						out.println("<table><tr>");
						
						//grab each of the headers, put them in their own cell
						for (int i=1; i <= resSetMetaDataAgencies.getColumnCount(); i++)
						{
							String header = resSetMetaDataAgencies.getColumnName(i);
							if (i == 1)
							{
								out.println("<th class=\"first\">" + header + "</th>");
							}
							else
							{
								out.println("<th>" + header + "</th>");
							}
						}	
						//print the end of the row
						out.println("</tr>");
					}
					//set a counter flag
					int counterAgencies = 1;
					String rowTypeAgencies;
					
					while (resSetAgencies.next())
					{
						//evaluate if the counter is odd or even
						if (counterAgencies%2 == 1)
						{
							//result is odd, so make the css class match
							rowTypeAgencies = "\"row-odd\"";
						}
						else
						{
							rowTypeAgencies = "\"row-even\"";
						}
						
						//print the beginning of the row
						out.println("<tr class=" + rowTypeAgencies + ">");
						
						//grab each of the column data, put them in their own cell
						
						for (int r=1; r <= resSetMetaDataAgencies.getColumnCount(); r++)
						{
							String cellAgencies = "";
							if (resSetAgencies.getString(r) != null)
							{
								cellAgencies = resSetAgencies.getString(r);
							}
							String openingTdAgencies = "<td>";
							if (r == 1)
							{
								openingTdAgencies = "<td class=\"first\">";
							}
							//if it's the last column (the agency one), determine if it's Calgary or Okotoks
							if (r != 1)
							{
								cellAgencies = "(" + cellAgencies.substring(0, 3) + 
								") " + cellAgencies.substring(3, 6) + "-" + cellAgencies.substring(6);
							}
							out.println(openingTdAgencies + cellAgencies + "</td>");
						}	
						//print the end of the row
						out.println("</tr>");
					}
					//end the table
					out.println("</table>");
					
					//close connection
					connectAgencies.close();
			 %>
			
								
			<h2>Agents</h2>
			<%
					//connect to database using web.xml stored parameters
					Class.forName(driver);
					Connection connect = DriverManager.getConnection(connection, user, password);
					java.sql.Statement stmnt = connect.createStatement();
					
					/*      ResultSet resSet = stmnt.executeQuery("SELECT agtfirstname || ' ' || agtlastname as \"Name\", " +
							"agtemail as \"Email\", agtbusphone as \"Phone\", agtposition as \"Position\"," +
							"agencyid as \"Agency\" from ictoosd.agents order by agencyid, agtlastname");
					*/
					
					//redone statement to pull city name
					ResultSet resSet = stmnt.executeQuery("SELECT at.agtfirstname || ' ' || at.agtlastname as \"Name\", " + 
							"at.agtemail as \"Email\", at.agtbusphone as \"Phone\", at.agtposition as \"Position\",  " +
							"ac.agncycity as \"Agency\" from ictoosd.agents at, ictoosd.agencies ac " +
							"where at.agencyid = ac.agencyid order by ac.agencyid, at.agtlastname");
					
					
					ResultSetMetaData resSetMetaData = resSet.getMetaData();
					
					if (resSetMetaData != null)//(resSet.next())
					{
						//print the beginning of the table and row
						out.println("<table><tr>");
						
						//grab each of the headers, put them in their own cell
						for (int i=1; i <= resSetMetaData.getColumnCount(); i++)
						{
							String header = resSetMetaData.getColumnName(i);
							if (i == 1)
							{
								out.println("<th class=\"first\">" + header + "</th>");
							}
							else
							{
								out.println("<th>" + header + "</th>");
							}
						}	
						//print the end of the row
						out.println("</tr>");
					}
					//set a counter flag
					int counter = 1;
					String rowType;
					
					while (resSet.next())
					{
						//evaluate if the counter is odd or even
						if (counter%2 == 1)
						{
							//result is odd, so make the css class match
							rowType = "\"row-odd\"";
						}
						else
						{
							rowType = "\"row-even\"";
						}
						
						//print the beginning of the row
						out.println("<tr class=" + rowType + ">");
						
						//grab each of the column data, put them in their own cell
						
						for (int r=1; r <= resSetMetaData.getColumnCount(); r++)
						{
							String cell = "";
							if (resSet.getString(r) != null)
							{
								cell = resSet.getString(r);
							}
							String openingTd = "<td>";
							if (r == 1)
							{
								openingTd = "<td class=\"first\">";
							}
							out.println(openingTd + cell + "</td>");
						}	
						//print the end of the row
						out.println("</tr>");
					}
					//end the table
					out.println("</table>");
					
					//close connection
					connect.close();
			 %>
			<br/>
			<br/>
		</div>
<!-- main ends -->	

